/* @(#)GSOperFunctionDAO.java    
 *
 * Project: BUS
 *
 * Modify Information:
 * =============================================================================
 *   Author       Date       Description
 *   ------------ ---------- ---------------------------------------------------
 *   simon yi     2006-2-10   新增
 *   jenny li     2006-2-22   修改getSubOrg
 *   Jenny Li     2006-3-10   添加方法queryUpperOrg
 * Copyright Notice:
 * =============================================================================
 *    Copyright (c) 2001-2006 Beijing HiTRUST Technology Co., Ltd. 1808 Room, 
 *        Science & Technology Building, No. 9 South Street, Zhong Guan Cun, 
 *        Haidian District, Beijing ,100081, China All rights reserved.
 *
 *    This software is the confidential and proprietary information of
 *        Beijing HiTRUST Technology Co., Ltd. ("Confidential Information").
 *        You shall not disclose such Confidential Information and shall use it
 *        only in accordance with the terms of the license agreement you entered
 *        into with HiTRUST.
 *
 * Warning:
 * =============================================================================
 * 
 */
package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.hitrust.gs.bean.GSOrganizationBean;
import com.hitrust.gs.bean.GSOrderDeptInfoBean;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;

/**
 * @author simonyi
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Style - Code Templates
 */
public class GSOrganizationDAO extends GSBaseDAO {

	public ArrayList queryAllOrganization(Connection aConn,String aOrgId) throws GSException{
		ArrayList tList = null;
		String tSql = "select * from GS_ORGANIZATION where GS_ORG_ID <>'0000' and GS_ORG_ID <>'" + aOrgId + "' order by gs_org_name";
		Object tObj[] = null;
		try{
			tList = executeQuery(new GSOrganizationBean(),tSql,tObj);
		}catch(Exception e){
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		return tList;
	}
	/**
	 * 查询所有底层节点
	 * */
	public ArrayList queryAllOrganization2(Connection aConn) throws GSException{
        ArrayList tList = null;
        String tSql = "select * from GS_ORGANIZATION where gs_org_id not in (select gs_upperorg_id from GS_ORGANIZATION)";
        Object tObj[] = null;
        try{
            tList = executeQuery(new GSOrganizationBean(),tSql,tObj);
        }catch(Exception e){
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tList;
    }
    /**
     * 读取机构
     * @param orgId
     * @return
     * @throws GSException
     */
    public GSOrganizationBean getOrg(Connection aConn,String orgId) throws GSException {
        String tSql = "select * from GS_Organization where GS_ORG_ID = ? order by gs_org_name";
        ArrayList tList = null;
        GSOrganizationBean tGSOrganizationBean = null;
        Object[] tObj = {orgId};
        try{
            tList = this.executeQuery(aConn,new GSOrganizationBean(),tSql,tObj);
            if(tList.size() == 1){
                tGSOrganizationBean = (GSOrganizationBean)tList.get(0);
            }else{
                throw new GSException(GSException.E20031,GSException.E20031_MSG);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        
        
        return tGSOrganizationBean;
    }
    
    /**
     * 查询所有机构，过滤虚拟机构0000(巴士物资)
     * @return
     * @throws GSException
     */
    public ArrayList getAllOrg() throws GSException {
		String tSql = "select * from GS_Organization where GS_ORG_ID <> '0000' order by gs_org_name";
		ArrayList tList = null;
		try{
			tList = this.executeQuery(new GSOrganizationBean(),tSql,null);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		
		return tList;
    }
        

    /**
     * 计算一个机构的所有下属机构
     * 为订单录入设计，数据格式化处理放到业务方法里
     * @param orgId
     * @return
     * @throws GSException
     */
    public ArrayList getSubOrg(Connection aConn,String orgId) throws GSException {
        ArrayList tList = null;
        GSOrganizationBean tGSOrganizationBean = null;

        //机构编号非法
        if(orgId == null || orgId.length()!=4)
            throw new GSException(GSException.E20032,GSException.E20032_MSG);
        
        //机构编号为00xx，表示巴士物资的部门
        //拥有选择巴士物资所有部门及下属公司/修理厂/部门的权限
        if("00".equals(orgId.substring(0,2))){

            tList = this.queryAllOrganization(aConn,orgId);
        }
        else{
            //否则读取自己和下属机构
            String tSql = "select * from GS_Organization where GS_UPPERORG_ID = ? order by gs_org_name";
            Object[] tObj = {orgId};
            try{
                tList = this.executeQuery(aConn,new GSOrganizationBean(),tSql,tObj);
            }catch(Exception e){
                GSLogWriter.error(this,e.toString());
                throw new GSException(GSException.E19999,GSException.E19999_MSG);
            }
            if(tList.size() > 0){
                for(int i=0;i<tList.size();i++){
                    tGSOrganizationBean = (GSOrganizationBean)tList.get(i);
                    ArrayList ary = getSubOrg(aConn,tGSOrganizationBean.getGs_org_id());
                    for(int j=0;j<ary.size();j++)
                        tList.add(ary.get(j));
                }
            }
        }

        return tList;
    }
    /**
     * 查询一个机构的上级机构
     * @param orgId
     * @return
     * @throws GSException
     */
    public GSOrganizationBean getParentOrg(String orgId) throws GSException {
		String tSql = "select * from GS_Organization where GS_ORG_ID = ( select GS_UPPERORG_ID from GS_Organization where GS_ORG_ID = ?)";
		ArrayList tList = null;
		GSOrganizationBean tGSOrganizationBean = null;
		Object[] tObj = {orgId};
		try{
			tList = this.executeQuery(new GSOrganizationBean(),tSql,tObj);
		    tGSOrganizationBean= (GSOrganizationBean)tList.get(0);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
				
		return tGSOrganizationBean;
    }
    
    /**
     * 机构是否存在
     * @param orgId
     * @return
     * @throws GSException
     */
	public boolean isExist(String orgId) throws GSException{
		String tSql = "select * from GS_Organization where GS_Org_ID = ?";
		boolean tFlag = false;
		Object[] tObj = {orgId};
		try{
			ArrayList tList = this.executeQuery(new GSOrganizationBean(),tSql,tObj);
			if(tList.size() > 0){
				tFlag = true;
			}
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}
		return tFlag;
	}
	
	/**
	 * 查询一个机构的所有常用联系方式
	 * 为订单录入设计，有数据格式化处理
	 * @param orgId
	 * @return
	 * @throws GSException
	 */
	public ArrayList getDeptInfoList(String orgId) throws GSException {
	    String tSql = "select * from GS_ORDER_DEPT_INFO where GS_ORG_ID = ?";
		Object[] tObj = {orgId};
		ArrayList tList = null;
		ArrayList tempList = new ArrayList();
		try{
			tList = this.executeQuery(new GSOrderDeptInfoBean(),tSql,tObj);
		}catch(Exception e){
			GSLogWriter.error(this,e.toString());
			throw new GSException(GSException.E19999,GSException.E19999_MSG);
		}	    
		for(int i=0;i<tList.size();i++){
		    GSOrderDeptInfoBean bean = (GSOrderDeptInfoBean)tList.get(i);
		    String content = "";

		    //格式化常用联系方式对象为字符串"addr_id|addr|name|phone"的形式
		    content = bean.getGs_deliver_goods_addr_id()+"|"+
		    bean.getGs_deliver_goods_addr()+"|"+
		    bean.getGs_order_contact_name()+"|"+
		    bean.getGs_order_contact_phone();
		    tempList.add(content);
		}
		return tempList;
	}
    
    /**
     * 通过机构编号和机构名称查询所有机构
     * String aOrgId
     * String aOrgName
     * @return ArrayList
     * @throws GSException
     */
    public ArrayList queryOrg(String aOrgId,String aOrgName) throws GSException {
        String tSql = "select * from GS_Organization where GS_ORG_ID like " +
                "'%" + aOrgId+ "%' and GS_ORG_NAME like '%" + aOrgName+ "%' order by GS_ORG_NAME";
        ArrayList tArrayList = null;
        Object[] tObject = {};
        try {
            tArrayList = executeQuery(new GSOrganizationBean(), tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }
        return tArrayList;
     }
    
    /**
     * 找到所有运营公司和巴士物资的机构代码
     * 
     * @return ArrayList
     * @throws GSException
     */
    public ArrayList queryUpOrg() throws GSException {
        String tSql = "select * from GS_ORGANIZATION where GS_ORG_ID like '%00' order by GS_ORG_NAME";
        ArrayList tArrayList = null;
        Object[] tObject = {};
        try {
            tArrayList = executeQuery(new GSOrganizationBean(), tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }
        return tArrayList;
     }
    /**
     * 在组织机构信息表GS_ORGANIZATION中新增一笔记录
     * 
     * @param aMerTempBean
     * 
     * return boolean
     * 
     * @throws CUPSException
     */
    public boolean add(GSOrganizationBean aGSOrganizationBean) throws GSException {  
        
        String tSql="INSERT INTO GS_ORGANIZATION (GS_ORG_ID,GS_ORG_NAME,GS_ORG_ADDR," +
                "GS_ORG_POST,GS_ORG_PHONE,GS_ORG_FAX,GS_PRINCIPAL_NAME," +
                "GS_PRINCIPAL_PHONE,GS_PRINCIPAL_EMAIL,GS_UPPERORG_ID,GS_IS_STOCK) " + 
                " VALUES(?,?,?,?,?,?,?,?,?,?,?)";

        String sql = "insert into gs_seqnum_manage (gs_seq_category_id,gs_seq_category_name,gs_current_seq,gs_org_id) " +
        		"values (?,?,?,?) ";
        Object[] tObject = new Object[11] ;
          tObject[0]=aGSOrganizationBean.getGs_org_id();
          tObject[1]=aGSOrganizationBean.getGs_org_name();
          tObject[2]=aGSOrganizationBean.getGs_org_addr();
          tObject[3]=aGSOrganizationBean.getGs_org_post();
          tObject[4]=aGSOrganizationBean.getGs_org_phone();
          tObject[5]=aGSOrganizationBean.getGs_org_fax();
          tObject[6]=aGSOrganizationBean.getGs_principal_name();
          tObject[7]=aGSOrganizationBean.getGs_principal_phone();
          tObject[8]=aGSOrganizationBean.getGs_principal_email();
          tObject[9]=aGSOrganizationBean.getGs_upper_orgId();
          tObject[10]=aGSOrganizationBean.getGs_is_stock();
          
        Object[] object1 = new Object[]{"01","正常采购","0",aGSOrganizationBean.getGs_org_id()};
        Object[] object2 = new Object[]{"02","应急采购","0",aGSOrganizationBean.getGs_org_id()};
        Connection conn = null; 
        boolean tResult = false;
        try {
            conn = this.getConnetion();
            tResult = executeUpdate(conn,tSql, tObject);
            tResult = tResult && executeUpdate(conn,sql,object1);
            tResult = tResult && executeUpdate(conn,sql,object2);

        } catch (Exception ex) {
            GSLogWriter.error(this, ex.toString());
            throw new GSException(ex.getMessage());
        }finally{
            close(conn);
        }

        return tResult;
    }
    
    
    /**
     * 在组织机构信息表GS_ORGANIZATION中更新一笔记录
     * 
     * @param MerchantTempBean
     * @throws CUPSException]
     *             return boolean
     */
    public boolean modify(GSOrganizationBean aGSOrganizationBean) throws GSException {

        String tSql = "UPDATE GS_ORGANIZATION SET GS_ORG_NAME = ?,GS_ORG_ADDR=?," +
                "GS_ORG_POST=?,GS_ORG_PHONE=?,GS_ORG_FAX=?,GS_PRINCIPAL_NAME=?," +
                "GS_PRINCIPAL_PHONE=?,GS_PRINCIPAL_EMAIL=?,GS_UPPERORG_ID=?, " + 
                "GS_IS_STOCK=? WHERE GS_ORG_ID = ?";
        Object[] tObject = new Object[11];
        tObject[0] = aGSOrganizationBean.getGs_org_name();
        tObject[1] = aGSOrganizationBean.getGs_org_addr();
        tObject[2] = aGSOrganizationBean.getGs_org_post();
        tObject[3] = aGSOrganizationBean.getGs_org_phone();
        tObject[4] = aGSOrganizationBean.getGs_org_fax();
        tObject[5] = aGSOrganizationBean.getGs_principal_name();
        tObject[6] = aGSOrganizationBean.getGs_principal_phone();
        tObject[7] = aGSOrganizationBean.getGs_principal_email();
        tObject[8] = aGSOrganizationBean.getGs_upper_orgId();
        tObject[9] = aGSOrganizationBean.getGs_is_stock();
        tObject[10] = aGSOrganizationBean.getGs_org_id(); 
        
        boolean tResult = false;
        try {
            tResult = executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
    /**
     * 在组织机构信息表GS_ORGANIZATION中删除一笔记录
     * 
     * @param aSerialNo
     *            序列号 省代码
     */
    public boolean delete(String aOrgId) throws GSException {
        String tSql = "delete from GS_ORGANIZATION where GS_ORG_ID=?";
        Object[] tObject = { aOrgId };
        boolean tResult = false;
        try {
            tResult=executeUpdate(tSql, tObject);
        } catch (Exception ex) {
            GSLogWriter.error(this, ex.getMessage());
            throw new GSException(ex.getMessage());
        }

        return tResult;
    }
    
	/**
	 * 设置组织机构信息表GS_ORGANIZATION中删除的记录对应的操作员为无效
	 * 
	 * @param aSerialNo
	 *            序列号 省代码
	 */
	public boolean setOperValidity(String aOrgId) throws GSException {
		String tSql = "update GS_OPERATOR set GS_OPER_VALIDITY='1' where GS_OPER_ORG_ID=?";
		Object[] tObject = { aOrgId };
		boolean tResult = false;
		try {
			tResult=executeUpdate(tSql, tObject);
		} catch (Exception ex) {
			GSLogWriter.error(this, ex.getMessage());
			throw new GSException(ex.getMessage());
		}

		return tResult;
	}    
    
    /**
     * 读取机构详细信息
     * @param orgId
     * @return
     * @throws GSException
     */
    public GSOrganizationBean getOrg(String aOrgId) throws GSException {
        String tSql = "select * from t_warehouse where code = ?";
        ArrayList tList = null;
        GSOrganizationBean tGSOrganizationBean = null;
        Object[] tObj = {aOrgId};
        try{
            tList = this.executeQuery(new GSOrganizationBean(),tSql,tObj);
            if(tList.size() == 1){
                tGSOrganizationBean = (GSOrganizationBean)tList.get(0);
            }else{
                throw new GSException(GSException.E20031,GSException.E20031_MSG);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        
        
        return tGSOrganizationBean;
    }
    /**
     * 查询组织的上层组织
     * @param aOrgId            组织编码
     * @return
     * @throws GSException
     */
    public GSOrganizationBean queryUpperOrg(String aOrgId) throws GSException{
        GSOrganizationBean tOrgBean = null;
        String tSql = "select * from GS_ORGANIZATION where GS_ORG_ID = (select GS_UPPERORG_ID from GS_ORGANIZATION where GS_ORG_ID=?)";
        Object[] tObj = {aOrgId};
        try{
            ArrayList tList =  this.executeQuery(new GSOrganizationBean(),tSql,tObj);
            if(tList.size() == 1){
                tOrgBean = (GSOrganizationBean)tList.get(0);
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tOrgBean;
    }
    
    
    public ArrayList getOrgAndOper() throws GSException {

        String sql = "select a.gs_org_id,a.gs_org_name,b.gs_oper_id,b.gs_oper_name " +
        		" from gs_organization a,gs_operator b where a.gs_org_id = b.gs_oper_org_id" +
        		" order by gs_org_name";
        Connection conn = null;
        HashMap map1 = new HashMap();
        HashMap map2 = new HashMap();
        ArrayList orglist = new ArrayList();
        ArrayList ary = new ArrayList();
        try{
            conn = this.getConnetion();
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            if(rs!=null){
                while(rs.next()){
                    String orgId = rs.getString(1);
                    String orgName = rs.getString(2);
                    String[] oper = new String[]{rs.getString(3),rs.getString(4)};

					// add by Jackey start
					// 用ArrayList记录组织机构图
					String[] org = new String[]{orgId,orgName};
					boolean isContain = false;
					for(int i=0;i<orglist.size();i++)
					{
						String[] orgval = (String[])orglist.get(i);
						if (orgval[0].equals(orgId))
						{
							isContain = true;
							break;
						}
					}
					if (!isContain) orglist.add(orglist.size(),org);
                    
					// add by Jackey end

                    
                    map1.put(orgId,orgName);                    
                    if(map2.get(orgId) == null){
                        ArrayList tmp = new ArrayList();
                        tmp.add(oper);
                        map2.put(orgId,tmp);
                    }else{
                        ((ArrayList)map2.get(orgId)).add(oper);
                    }
                    
                }
            }
            
            rs.close();
            pstmt.close();
            ary.add(map1);
            ary.add(map2);
            ary.add(orglist);
            
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }finally{
            close(conn);
        }
        return ary;
    }
    /**
     * 查询机构名称
     * @param aOrgId		机构编码
     * @return
     * @throws GSException
     */
    public String queryOrgName(String aOrgId) throws GSException{
        String tName = "";
        String tSql = "select * from GS_ORGANIZATION where GS_ORG_ID='" + aOrgId + "'";
        try{
            ArrayList tList = this.executeQuery(new GSOrganizationBean(),tSql,null);
            if(tList.size() > 0){
                tName = ((GSOrganizationBean)tList.get(0)).getGs_org_name();
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }
        return tName;
    }
    
    public ArrayList getAllOrgForReport_old(String orgId) throws GSException {
        ArrayList map = new ArrayList();
        String sql = "";
        if("0000".equals(orgId) || (orgId != null && orgId.substring(0,2).equals("00"))){
            sql = "select a.gs_org_id P1,a.gs_upperorg_id P2,a.gs_org_name P3,b.gs_org_name P4 from gs_organization a,gs_organization b where a.gs_upperorg_id = b.gs_org_id order by a.gs_upperorg_id";    
        }else{
            sql = "select a.gs_org_id P1,a.gs_upperorg_id P2,a.gs_org_name P3,b.gs_org_name P4 from gs_organization a,gs_organization b where a.gs_upperorg_id = b.gs_org_id and b.gs_org_id = ? order by a.gs_upperorg_id";
        }
        
        Connection conn = this.getConnetion();
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,orgId);
            ResultSet rst = pstmt.executeQuery();
            if(rst != null){
	            while(rst.next()){
	                String[] tmp = new String[]{rst.getString(1),rst.getString(2),rst.getString(3),rst.getString(4)};
	                map.add(tmp);
	            }
	            rst.close();
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        return map;
    }

    public ArrayList getAllOrgForReport(String orgId) throws GSException {
        ArrayList ary = new ArrayList();
        HashMap map = new HashMap();
        String sql = "";
        if("0000".equals(orgId) || (orgId != null && orgId.substring(0,2).equals("00"))){
            sql = "select a.gs_org_id P1,a.gs_upperorg_id P2,a.gs_org_name P3,b.gs_org_name P4 from gs_organization a,gs_organization b where a.gs_upperorg_id = b.gs_org_id and (b.gs_org_id = ? or a.gs_upperorg_id = ? or 1=1) order by a.gs_upperorg_id";    
        }else{
            sql = "select a.gs_org_id P1,a.gs_upperorg_id P2,a.gs_org_name P3,b.gs_org_name P4 from gs_organization a,gs_organization b where a.gs_upperorg_id = b.gs_org_id and (a.gs_org_id = ? or a.gs_upperorg_id = ?) order by a.gs_upperorg_id";
        }
        
        Connection conn = this.getConnetion();
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,orgId);
            pstmt.setString(2,orgId);
            ResultSet rst = pstmt.executeQuery();
            if(rst != null){
                ArrayList arylist = null;
	            while(rst.next()){
	                String[] tmp = new String[]{rst.getString(1),rst.getString(2),rst.getString(3),rst.getString(4)};
	                if("0000".equals(tmp[1])){//上级机构为0000的
	                    if(tmp[0].substring(0,2).equals("00")){//处理巴士物资以及下属
	                        if(map.containsKey("0000")){//如果巴士物资矢量已经存在
	                            arylist = (ArrayList)map.get("0000");
	                            
	                        }else{//如果巴士物资矢量不存在
	                            arylist = new ArrayList();	 
	                            map.put("0000",arylist);
	                        }
	                        arylist.add(tmp);
	                    }else{//处理运营公司
	                        arylist = new ArrayList();
	                        arylist.add(tmp);
	                        map.put(tmp[0],arylist);
	                    }
	                }else{//上级机构不为0000，都是运营公司下属单位
	                    if(map.containsKey(tmp[1])){
	                        arylist = (ArrayList)map.get(tmp[1]);
	                    }else{
	                        arylist = new ArrayList();
	                        map.put(tmp[1],arylist);
	                    }
	                    arylist.add(tmp);
	                }
	            }
	            rst.close();
            }
        }catch(Exception e){
            GSLogWriter.error(this,e.toString());
            throw new GSException(GSException.E19999,GSException.E19999_MSG);
        }finally{
            GSOrganizationDAO.close(conn);
        }
        
        Iterator it = map.keySet().iterator();
        while(it.hasNext()){
            String s = (String)it.next();
            ArrayList tmpList = (ArrayList)map.get(s);
            ary.addAll(tmpList);
        }
        return ary;
    }

}
